<?php
// pdo_access.inc.php
/*
 * Copyright (C) 2004-2007 Manuel Kägi, kaegi(at)gmx(dot)ch
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or (at
 * your option) any later version.
 *
 * This program is distributed in the hope that it will be useful, but
 * WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */


/**
*	GENERAL NOTES
* ============================
*
*	1. This class requires methods form stdfuncs.inc.php (can be found at www.corvent.ch)
*		stdfuncs.inc.php will be looked up in a folder "core" going off from any php-include path.
*		If you dont want a subfolder for stdfuncs.inc.php just change the require_once argument.
*
*	2. Generrally you can access all DBMS for which a PDO-driver is available, but tested is only MySQL and SQLite
*		The class-Variable "specEscFunction" specifies the correct escaping functions according to the DBMS
*
*	3. The Values you get from getSQLResultsAsArray are formatted in a way to use them as data for all
*		tableXXXX in the class arrayfunc in stdfuncs.inc.php (tableSort, tableGroup etc..)
*
*	4. To inspect the results from any getSQLReusltXXXX() function use debug::show(), defined in stdfuncs.inc.php
*
*	5. All SQL-Staements will measure their time using the debug::startMessung and stopMessung methods
*		if you are intrested in these results you can print them to the standard Output using
*		debug::showLaufzeit ("Laufzeit" means "runtime" in german), You can do this after all your
*		Db-Operations are done, you will get a nicely formatted overview over all SQL-Statements and their 
*		runtime consumption.
*		This measurings need very little time itself, if you eigther want to switch them off just
*		remove all "debug::startMessung" and "debug::stopMessung" commands 
*	
*/
require_once("core/stdfuncs.inc.php");

class pdo_access {
	
	
	var $db;
	var $debugMode = true;
	
	var $dsn;
	var $user;
	var $pw;
	
	var $escapingFunction = "addslashes";
	var $specEscFunction = array('sqlite'=>'sqlite_escape_string', 'sqlite2'=>'sqlite_escape_string');
	
	/** 
	*	Instantiates a pdo_access Object and connects to the specified Database Server 
	*		
	*	@param string $dbServerSpec the dsn of the database to connect with 
	*		{dbType}:{dsn}
	*	@param string $user the username (if you use a password protected database)
	*	@param szring $pw: the password to use
	*/
	function pdo_access($dbServerSpec, $user="", $pw="") {
		$this->dsn = $dbServerSpec;
		$this->user = $user;
		$this->pw = $pw;

		try { 
			// $this->db = new PDO($dbServerSpec, $user, $pw, array(PDO::ATTR_PERSISTENT => true));
			$this->db = new PDO($dbServerSpec, $user, $pw);
			$this->db->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL);	
		}
		catch (PDOException $e) {
  			print("pdo_access: Connection failed: ".$e->getMessage()."\n");
  			print("DSN = '".$this->dsn."'\n");
		}		
	}
	
	//	SELECT SQL's
	// =======================
	
	/**
	*	Performs a SELECT query and returns the result as an array of hashtables representing the records
	*
	*	@param string $sql, the SQL to perform (e.g. "SELECT * FROM table")
	*	@param string $keyCol - OPTIONAL - if set the value of the specified column will be used as index in the array of the record-Haschtables, 
	*			typically "id" OR "username", this column should be unique over the whole result of the query, 
	*			otherwise you wont get all records, the later ones with the same value in the keyCol will
	*			overwrite the earlier ones
	*
	*			If $keyCol is not set the indizes of the records will go from 0 to the queryresult's length - 1
	*
	*	@return array an array of haschtables representing the result of the query
	*/
	function getSQLResultAsArray($sql, $keyCol = "") {
		debug::startMessung("getSQLResultAsArray");
		$result = $this->select($sql, PDO::FETCH_ASSOC);
		
		// debug::show($result, "result");
		
		if ($keyCol != "") {
			$hashedResult = array();
			foreach ($result as $row) {
				$key = $row[$keyCol];
				unset($row[$keyCol]);
				$hashedResult[$key] = $row;
			}
			$result = $hashedResult;
		}
		
		debug::stopMessung("getSQLResultAsArray");
		return $result;
	}	

	/**
	*	Performs a SELECT query and returns the result as a Hashtable
	*
	*	The values of the first column will bey used as Key of the Hashtable
	*	If the COLUMN-Count is 2, the value of second Column will be used as value, this is the intended way to use this method
	*	If the COLUMN-Count is 1, the value will always be the same as the key
	*	If the COLUMN-Count is greater than 2 the value will be the whole record (as a hashtable)
	*
	*	@param string $sql, the SQL-Query to perform, typically "SELECT id, name FROM users";
	*
	*	@return array the result as hashtable
	*/
	function getSQLResultAsHashtable($sql) {
		debug::startMessung("getSQLResultAsHashtable");
		$colCount = 0;
		$result = array(); 
		$numericArray = $this->select($sql, PDO::FETCH_NUM);
		if (count($numericArray > 0)) $colCount = count($numericArray[0]);
		foreach ($numericArray as $row) {
			$key = $row[0];

			if ($colCount == 2) $value = $row[1];
			if ($colCount > 2) $value = $row;
			if ($colCount == 1) $value = $key;

			$result[$key] = $value;
		}
		debug::stopMessung("getSQLResultAsHashtable");	
		return $result;
	}	
	
	/**
	*	Performs a SELECT query and returns values found in the firs Column as numeric array
	*
	*	@param string $sql, the SQL-Query to perform, typically "SELECT name FROM users";
	*
	*	@return array, the first-col values
	*/
	function getSQLResultFirstCol($sql) {
		debug::startMessung("getSQLResultFirstCol");
		$result = array();
		$numericArray = $this->select($sql, PDO::FETCH_NUM);
		foreach ($numericArray as $row) {
			$result[] = $row[0];
		}
		debug::stopMessung("getSQLResultFirstCol");	
		return $result;
	}

	/**
	*	Performs a SELECT query and returns the found (first) record as hashtable
	*	The query itself should be designed so that its result will countain only one record
	*
	*	@param string $sql, the SQL-Query to perform, typically "SELECT * FROM users WHERE id='12'";
	*	@param boolean $allowMultipleMatch, - OPTIONAL - if the method schould ignore if there are more than one record in the querys result
	*		- true => if a query brings more than one record, the first one is returned
	*		- false => if a query brings more than one record, boolean false is returned
	*			The intention is to indicate possible inconsitency of the database
	*			This behavior is default
	*
	*	@return array, the record as hashtable
	*/
	function getSQLResultFirstRow($sql, $allowMultipleMatch = false) {
		debug::startMessung("getSQLResultFirstRow");
		$assocArray = $this->select($sql, PDO::FETCH_ASSOC);
		debug::stopMessung("getSQLResultFirstRow");	
		if (count($assocArray) == 1) return $assocArray[0];
		if (count($assocArray) > 1 && $allowMultipleMatch) $assocArray[0];
		return false;
	}

	/**
	*	Performs a SELECT query and returns the first cell of  found (first) record.
	*
	*	@param string $sql, the SQL-Query to perform, typically "SELECT name FROM users WHERE id='12'";
	*	@param boolean $allowMultipleMatch, - OPTIONAL - if the method schould ignore if there are more than one record in the querys result
	*		- true => if a query brings more than one record, the first cell of the first record is returned
	*		- false => if a query brings more than one record, boolean false is returned
	*			The intention is to indicate possible inconsitency of the database
	*			This behavior is default
	*
	*	@return string, the found value
	*
	*
	*/
	function getSQLResultFirstCell($sql, $allowMultipleMatch = false) {
		debug::startMessung("getSQLResultFirstCell");
		$numericArray = $this->select($sql, PDO::FETCH_NUM);
		debug::stopMessung("getSQLResultFirstCell");	
				
		if (count($numericArray) == 1) return $numericArray[0][0];
		if (count($numericArray) > 1 && $allowMultipleMatch) $numericArray[0][0];
		return false;
	}
	
	
	//	COUNT / DISTINCT SQL's
	// ===================================

	/**
	*	Counts all records of a table that fulfill the given condition
	*
	*	@param string $table, the table to query
	*	@param string $cond - OPTIONAL - the condition the records to count must fulfill
	*		- default: no condition, all records of the table will be counted
	*
	*	countRecords($table, $cond) 
	*		is a shortcut for
	*	getSQLResultFirstCell("SELECT COUNT(*) FROM $table WHERE $cond");
	*	
	*	@return int the number of records found
	*/
	function countRecords($table, $cond = "") {
		$sql = "SELECT COUNT(*) FROM $table";
		if ($cond != "") $sql.=" WHERE $cond";
		$count = $this->getSQLResultFirstCell($sql);
		if (! $count === false) $count = (int) $count;
		return $count;
	}

	/**
	*	Returns all different values found in a column of a table
	*	Optionally a condition that the records must fulfill can be given
	*
	*	@param string $table, the table to query
	*	@param string $column, the column whose distinct values interest
	*	@param string $cond - OPTIONAL - the condition the records to count must fulfill
	*
	*	getDistinctValues($table, $column, $cond) 
	*		is a shortcut for
	*	getSQLResultFirstCol("SELECT DISTINCT($column) FROM $table WHERE $cond");
	*/
	function getDistinctValues($table, $column, $cond = "") {
		$sql = "SELECT DISTINCT($column) FROM $table";
		if ($cond != "") $sql.=" WHERE $cond";
		return $this->getSQLResultFirstCol($sql);
	}

	/**
	*	Counts all different values found in a column of a table
	*	Optionally a condition that the records must fulfill can be given
	*
	*	@param string $table, the table to query
	*	@param string $column, the column whose distinct values interest
	*	@param string $cond - OPTIONAL - the condition the records to count must fulfill
	*
	*	countDistinctValues($table, $column, $cond) 
	*		is a shortcut for
	*	getSQLResultFirstCell("SELECT COUNT(DISTINCT($column)) FROM $table WHERE $cond");
	*/
	function countDistinctValues($table, $column, $cond = "") {
		$sql = "SELECT COUNT(DISTINCT($column)) FROM $table";
		if ($cond != "") $sql.=" WHERE $cond";
		$count = $this->getSQLResultFirstCell($sql);
		if (! $count === false) $count = (int) $count;
		return $count;
	}



	//	INSERT / UPDATE / DELETE
	// ===================================

	/**
	*	Inserts values, given as hashtable into the given table
	*	The keys of the hashtable MUST represten the columnnames of the table
	*	Keys that do not exist as column in the table will lead to an SQL-Error!
	*	The values will be escaped according to the used databse system (actully the known DBMS ar MySQL and SQLite
	*	Values given in the third argument (also a hashtable) will not be escaped
	*	
	*	@param string $tablename, the name of the table to insert the values into
	*	@param array $valueArray, the values to inser as hashtable (associative array) 
	*	@param array $functions, - OPTIONAL - the unescaped values to insert as hashtable
	*		e.g. $functions = array('pw' => "PASSWORD('secret')");
	*		if this would be in the $valueArray argument, "PASSWORD('secret')" would be inserted what hardly will be your intention
	*
	*	@return int the inserted id if an auto_increment column is in the table
	*		int the number of inserted rows if no auto_increment col available
	*		boolean false on error
	*/
	function insertValues($tablename, $valueArray, $functions = array()) {
		debug::startMessung("insertValues");
		$clist = ""; $vlist = "";
		if (is_array($valueArray)) {
			foreach ($valueArray as $col => $value) {
				$functions[$col] = "'".$this->escapeForDbQuery($value)."'";
				if (is_null($value)) $functions[$col] = "NULL";
			}
		}

		foreach ($functions as $col => $value) {
			$clist.= "$col, ";
			$vlist.= "$value, ";
		}
		$clist = substr($clist, 0, -2);
		$vlist = substr($vlist, 0, -2);
		$sql = "INSERT INTO $tablename ($clist) VALUES ($vlist)";
		debug::stopMessung("insertValues");	
		return $this->insert($sql, $tablename);
	}

	/**
	*	Updates values, given as hashtable in the given table in every record that fulfills the given condition
	*	The keys of the hashtable MUST represten the columnnames of the table
	*	Keys that do not exist as column in the table will lead to an SQL-Error!
	*	The values will be escaped according to the used databse system (actully the known DBMS ar MySQL and SQLite
	*	Values given in the forth argument (also a hashtable) will not be escaped
	*	
	*	@param string $tablename, the name of the table to updete the values in
	*	@param string $condition, coundtion the records must fulfill to be updated (e.g. "id=12")
	*	@param array $valueArray, new values as hashtable (associative array) 
	*	@param array $functions, - OPTIONAL - the unescaped new values as hashtable
	*		e.g. $functions = array('pw' => "PASSWORD('secret')");
	*		if this would be in the $valueArray argument, the "pw" field would be updated to "PASSWORD('secret')" what hardly will be your intention
	*
	*	@return int the number of updated records
	*		boolean false on error
	*		
	*	NOTES:
	*		- use "===" to check for failures, integer 0 would mean: no SQL-Error, but no record found 
	*			that fulfills the given condition
	*		- if the condition is empty nothing will be done to avoid that you update a whole 
	*			table to the same values by a mistake. If you want to do so give something like
	*			"true" or "1 = 1" as condition
	*/
	function updateValues($tablename, $condition, $valueArray, $functions=array()) {	
		debug::startMessung("updateValues");
		
		$list = "";
		if (is_array($condition)) {
			foreach ($condition as $col => $value) $c[] = "$col='".$this->escapeForDbQuery($value)."'";
			$condition = implode(" AND ", $c);
		}

		debug::startMessung($tablename." WHERE ".$condition);
		if (is_array($valueArray)) {
			foreach ($valueArray as $col => $value) {
				$functions[$col] = "'".$this->escapeForDbQuery($value)."'";
				if (is_null($value)) $functions[$col] = "NULL";
			}
		}
		foreach ($functions as $col => $value)
			$list.= "$col=$value, ";
		$list = substr($list, 0, -2);
		$retVal = false;
		
		if ($condition != "") {		// Damit nicht die ganze Tabelle verholzt werden kann!!
			$sql = "UPDATE $tablename SET $list WHERE $condition";
			$retVal = $this->execute($sql);
		}

		debug::stopMessung($tablename." WHERE ".$condition);
		debug::stopMessung("updateValues");	
		return $retVal;
	}
	
	/**
	*	Escapes values for the update- and insert querys
	*	Normally "addslashes", in case of an SQLite DBMS "sqlite_escape_string" is used
	*
	*	@param string $value, the unescaped value
	*	@return string the escaped value
	*/
	public function escapeForDbQuery($value) {
		$escFunction = $this->escapingFunction;
		$serverType = substr($this->dsn, 0, strpos($this->dsn, ":"));
		if (isset($this->specEscFunction[$serverType])) $escFunction = $this->specEscFunction[$serverType];
		
		return $escFunction($value);
	}
	
	/**
	*	Deletes all records from a table that fulfill the given condition
	*
	*	@param string $tablename, the name of the table to delete records from
	*	@param string $condition, coundtion the records must fulfill to be deleted (e.g. "id=12")
	*
	*	@return int the number of updated records
	*		boolean false on error
	*
	*	NOTES:
	*		- use "===" to check for failures, integer 0 would mean: no SQL-Error, but no record found 
	*			that fulfills the given condition, and so nothing deleted
	*		- if the condition is empty nothing will be done to avoid that you update a whole 
	*			table to the same values by a mistake. If you want to do so give something like
	*			"true" or "1 = 1" as condition
	*/
	function delete($table, $cond) {
		if (is_array($cond)) {
			foreach ($cond as $col => $value) $c[] = "$col='".$this->escapeForDbQuery($value)."'";
			$cond = implode(" AND ", $c);
		}
		$affectedRows = false;
		if ($cond != "") $affectedRows = $this->execute("DELETE FROM $table WHERE $cond");
		return affectedRows;
	}

	/**
	*	Sets if on errors a debug-message should be printed to the standard output
	*	The default is true, set it to false in a productional environment.
	*
	*	@param boolen $debug, show errors or not.
	*/
	function showErrors($debug) {
		$this->debugMode = $debug;
	}
	
	
	
	//	INTERNA
	// ===================================
	
	// Führt einen Select SQL aus, gibt das resultat numerisch oder assoziative zurück
	//	für return_mode die PDO::FETCH_xxx Konstanten verwenden

	/**
	*	The internal function to perform SELECT-Querys
	*
	*	@param string $sql, the SQL to perform
	*	@return_mode the return mode according to the PDO->fetchAll function
	*	
	*	@return array an array of the results;
	*/
	private function select($sql, $return_mode) {
		$result = false;
		if (is_object($this->db)) {
			debug::startMessung("SQL: ".$sql);		
			$result = array();		
			$statement = $this->db->query($sql);
			if ($statement === false) {
				if ($this->handleError($sql) == true) {	
					$statement = $this->db->query($sql);				
				}
			}

			if ($statement === false) {
				$this->showError($sql);
			}
			else {
				$result = $statement->fetchAll($return_mode);
			}
			debug::stopMessung("SQL: ".$sql);
		}
		return $result;
		
	}

	/**
	*	Internal insrting function 
	*	
	*	@param $sql, the SQL-INSERT Statement
	*	
	*	@return int, the newly created id if an auto_increment col is in the table
	*		int, number of inserted rows if no auto_increment col
	*		boolean false on error
	*/
	private function insert($sql, $tablename="") {
		$retVal = $this->db->exec($sql);
		if ($retVal === false) {
			if ($this->handleError($sql) == true) {	
				$retVal = $this->db->exec($sql);				
			}
		}
		
		if ($retVal === false) {
			$this->showError($sql);			
		}
		else {
			if ($this->db->lastInsertId() > 0) $retVal = $this->db->lastInsertId();
		}
		return $retVal;
	}
	
	/**
	*	Internal SQL-Execution of UPDATE and DELETE-Statements
	*	
	*	@param $sql, the SQL Statement
	*	
	*	@return int, number of affected rows (updated oder deleted)
	*		boolean false on error
	*/
	public function execute($sql) {
		$affectedRows = $this->db->exec($sql);
		if ($affectedRows === false) {
			if ($this->handleError($sql) == true) {
				$affectedRows = $this->db->exec($sql);				
			}
		}
		if ($affectedRows === false) $this->showError($sql);			
		return $affectedRows;
	}
	
	/**
	*	Internal Error handling 
	*	Tries to reconnect on a failureCode HY000
	*
	*	@return if the SQL-Call should be tried again (i.e. a reconnect has been done)
	*/
	private function handleError($sql) {
		$this->showError($sql);
		
		$rawError = $this->db->errorInfo();
		$retry = false;		
		if ($rawError[0] == "HY000") {	
			// Dieser Fehlercode bedeutet dass dir Verbindung neu aufgebaut werden muss. 
			// Und zwar ohne persistent!
			// Problem PDO behaltet die Verbindungen sonst ewig (länger als 8h was mehr als der wait_timeout der DbServer ist)

			if ($this->debugMode) print("Try to reconnect...");
			
			$this->db = new PDO($this->dsn, $this->user, $this->pw);			
			//$this->db = new PDO($this->dsn, $this->user, $this->pw, array(PDO::ATTR_PERSISTENT => true));
			
			$retry = true;
		}

		return $retry;
	}
	
	/**
	*	shows a niocely formatted erroe message if an SQL-Statement fails if debugMode is true (default)
	*	@uses debug::show 
	*	@see stdfuncs.inc.php
	*	
	*	@param string $sql, the SQL that failed.
	*/
	private function showError($sql) {
		if ($this->debugMode) {
			$rawError = $this->db->errorInfo();
			$error['code'] = $rawError[0]." / ".$rawError[1];
			$error['sql'] = $sql;
			$error['message'] = $rawError[2];
			
			debug::show($error, "SQL-Fehler");
		}
	}


	//	HILFS-FUNKTION	
	// ===================================
	
	/**
	*	Creates from a user entered european date (i.e. "dd.mm.yyyy") an SQL-Date
	*	using wildcards (%)
	*
	*	e.g. 	%.%.2007 will get 2007-%-%
	*		10.%.2007 will get 2007-%-10
	*		10.2007 will get 2007-10-% NOTE the leading dot
	*		10.8 will get 10-08-2007 (in the year 2007 !) 	
	*		10.8.2007 gets 2007-08-10
	*	
	*	@param string $euroDate the in eurpean manner entered date
	*	
	*	@return string the wildcarded SQL-formatted date
	*/
	function createWildCardedSQLDate($euroDate) {
		$euroDate = trim($euroDate);
		if (substr($euroDate, strlen($euroDate) - 1) == "%") {
			$euroDate = substr($euroDate, 0, strlen($euroDate)-1);
		}
		if ($euroDate == "") {
			$sqlFilterDate = "%";
		}
		else {
	
			$ed = explode(".", $euroDate);
			if (count($ed) == 1) $ed[1] = date("m");	// Fehlenden Monat durach Aktuellen Monat ersetzen
			if (count($ed) == 2) $ed[2] = date("Y");	// Fehlendes Jahr durch aktuelles Ersetzen

			if ($ed[0] == "") $ed[0] = "%";			// Leerstring als Wildcard interpretieren
			if ($ed[1] == "") $ed[1] = "%";	
			if ($ed[2] == "") $ed[2] = "%";	
			if (strlen($ed[0]) == 1 && $ed[0] != "%") $ed[0] = "0".$ed[0];  // Falls Tag = [0..9] : zweistellig machen 
			if (strlen($ed[1]) == 1 && $ed[1] != "%") $ed[1] = "0".$ed[1];  // Falls Monat = [0..9] : zweistellig machen 

			if (strlen($ed[2]) == 1 && $ed[2] != "%") $ed[2] = substr(date('Y'), 0, 3).$ed[2];	// Jahr vierstellig machen
			if (strlen($ed[2]) == 2) $ed[2] = substr(date('Y'), 0, 2).$ed[2];	// Jahr vierstellig machen
			if (strlen($ed[2]) == 3) $ed[2] = substr(date('Y'), 0, 1).$ed[2];	// Jahr vierstellig machen

			krsort($ed);
			$sqlFilterDate = join("-", $ed);
		}
		
		// print("Aus $euroDate => $sqlFilterDate<br>\n");
		return $sqlFilterDate;
	}
	

}

?>
